package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.repositories.CRMRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Date;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2016/12/23.
 */
@Repository
@Transactional//(readOnly = true)
public class CRMRepositoryImpl extends BaseRepository implements CRMRepository {

    private static Logger logger = LoggerFactory.getLogger(CRMRepositoryImpl.class);

    @Override
    public PageData findSurveyInfo(String permit, String username, Date firstToShopDate, Date NPSDate, String stage, String cusName,
                                   String cusPhone, String researchState, String connectStartDate, String connectEndDate, String nowState,
                                   String intentCarVehicles, String intentCarStyle, String nextConnectStartDate, String nextConnectEndDate,
                                   String surveyResult, int pageSize, int pageIndex) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	queryResult.id, ");
        sb.append(" 	queryResult.stage, ");
        sb.append(" 	queryResult.stageName, ");
        sb.append(" 	queryResult.surveyResult, ");
        sb.append(" 	queryResult.valid, ");
        sb.append(" 	queryResult.custId, ");
        sb.append(" 	queryResult.cusName, ");
        sb.append(" 	queryResult.cusAppellation, ");
        sb.append(" 	queryResult.cusPhone, ");
        sb.append(" 	queryResult.firstStoreDate, ");
        sb.append(" 	queryResult.intentCarVehicles, ");
        sb.append(" 	queryResult.intentCarStyle, ");
        sb.append(" 	queryResult.scCode, ");
        sb.append(" 	queryResult.scName, ");
        sb.append(" 	queryResult.orderIntervalDays, ");
        sb.append(" 	queryResult.connectDate, ");
        sb.append(" 	queryResult.connectTime, ");
        sb.append(" 	queryResult.nextConnectDate, ");
        sb.append(" 	queryResult.serialNumber ");
        sb.append(" from ( ");

        sb.append(" SELECT ");
        sb.append(" 	'' id,  ");
        sb.append(" 	a.stage,  ");
        sb.append(" 	a.stageName,  ");
        sb.append(" 	'' surveyResult,  ");
        sb.append(" 	'' valid,  ");
        sb.append(" 	a.custId,  ");
        sb.append(" 	b.`顾客姓名` cusName,  ");
        sb.append(" 	case b.`顾客性别` ");
        sb.append(" 		when '男' then '先生'  ");
        sb.append(" 		when '女' then '女士'  ");
        sb.append(" 	else '' end cusAppellation,  ");
        sb.append(" 	b.`电话号码` cusPhone,  ");
        sb.append(" 	IF(a.firstStoreDate = '',c.firstStoreDate, a.firstStoreDate) firstStoreDate,  ");
        sb.append(" 	IF(d.`来源` = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) intentCarVehicles,  ");
        sb.append(" 	d.`车型` intentCarStyle,  ");
        sb.append(" 	b.`销售顾问` scCode,  ");
        sb.append(" 	e.`人员姓名` scName,  ");
        sb.append(" 	'' orderIntervalDays,  ");
        sb.append(" 	'' connectDate,  ");
        sb.append(" 	'' connectTime,  ");
        sb.append(" 	'' nextConnectDate,  ");
        sb.append(" 	a.serialNumber  ");
        sb.append(" FROM ( ");
        sb.append(" 	SELECT a.* FROM ( ");
        sb.append(" 		SELECT ");
        sb.append(" 			'01' AS stage, ");
        sb.append(" 			'首次离店调研' AS stageName, ");
        sb.append(" 			a.`进店编号` AS serialNumber, ");
        sb.append(" 			a.`顾客编号` AS custId, ");
        sb.append(" 			a.`意向车型A` AS intentCarStyle, ");
        sb.append(" 			a.`进店日期` AS firstStoreDate ");
        sb.append(" 		FROM `顾客进店记录表` AS a ");
        sb.append(" 		WHERE a.`许可` = :permit ");
        sb.append(" 		AND a.`顾客编号` IS NOT NULL ");
        sb.append(" 		AND a.`是否首次到店` = '是' ");
        sb.append(" 		AND a.`进店日期` <= :firstToShopDate ");
        sb.append(" 		AND a.`接待类型` <> '未到店' ");
        sb.append(" 		AND a.`到店时间` <> '00:00:00' ");
        sb.append(" 		AND a.`到店时间` <> '01:00:00' ");
        sb.append(" 		AND a.`到店时间` <> '03:00:00' ");
        if ("EYZL10".equals(permit)) {
            sb.append(" 	and a.`进店日期` >= '2017-01-17' ");
        }
        sb.append(" 		UNION ALL ");
        sb.append(" 		SELECT ");
        sb.append(" 			'04' AS stage, ");
        sb.append(" 			'NPS调研' AS stageName, ");
        sb.append(" 			a.`订单编号` AS serialNumber, ");
        sb.append(" 			a.`顾客编号` AS custId, ");
        sb.append(" 			a.车型 AS intentCarStyle, ");
        sb.append(" 			'' AS firstStoreDate ");
        sb.append(" 		FROM `顾客订单记录` AS a ");
        sb.append(" 		WHERE a.`许可` = :permit ");
        sb.append(" 		AND a.`交车日期` IS NOT NULL ");
        sb.append(" 		AND a.`交车日期` <= :NPSDate ");
        sb.append(" 	) a ");
        sb.append(" 	LEFT JOIN ( ");
        sb.append(" 		SELECT b.* from crm_history AS b where b.permit = :permit AND (b.stage = '01' OR b.stage = '04') ");
        sb.append(" 	) b ON (a.stage = b.stage AND a.serialNumber = b.serialNumber) ");
        sb.append(" 	WHERE b.id IS NULL ");
        sb.append(" 	UNION ALL ");
        sb.append(" 	SELECT a.* FROM ( ");
        sb.append(" 		SELECT ");
        sb.append(" 			CASE a.`顾客状态` ");
        sb.append(" 				WHEN '休眠' THEN '02' ");
        sb.append(" 				WHEN '战败' THEN '03' ");
        sb.append(" 			ELSE '' END AS stage, ");
        sb.append(" 			CASE a.`顾客状态` ");
        sb.append(" 				WHEN '休眠' THEN '休眠调研' ");
        sb.append(" 				WHEN '战败' THEN '战败调研' ");
        sb.append(" 			ELSE '' END AS stageName, ");
        sb.append(" 			'' serialNumber, ");
        sb.append(" 			a.`顾客编号` AS custId, ");
        sb.append(" 			'' AS intentCarStyle, ");
        sb.append(" 			'' AS firstStoreDate ");
        sb.append(" 		FROM `顾客记录汇总` a ");
        sb.append(" 		WHERE a.`许可` = :permit ");
        sb.append(" 		AND (a.`顾客状态` = '休眠' OR a.`顾客状态` = '战败') ");
        sb.append(" 		AND a.`CRM战败确认` IS NULL ");
        sb.append(" 	) a ");
        sb.append(" 	LEFT JOIN ( ");
        sb.append(" 		SELECT * from crm_history b WHERE b.permit = :permit AND (b.stage = '02' OR b.stage = '03') ");
        sb.append(" 	) b ON (a.stage = b.stage AND a.custId = b.custId) ");
        sb.append(" 	WHERE b.id is null ");
        sb.append(" ) a ");
        sb.append(" JOIN `顾客记录汇总` as b on a.custId = b.`顾客编号`  ");
        sb.append(" LEFT JOIN `顾客进店记录表` as t on (a.stage = '01' AND a.serialNumber = t.`进店编号`)  ");
        sb.append(" LEFT JOIN ( ");
        sb.append(" 	SELECT a.`顾客编号`,MIN(a.`进店日期`) firstStoreDate FROM `顾客进店记录表` a ");
        sb.append(" 	WHERE a.`许可` = :permit ");
        sb.append(" 	AND a.`顾客编号` IS NOT NULL ");
        sb.append(" 	AND a.`是否首次到店` = '是' ");
        sb.append(" 	AND a.`接待类型` <> '未到店' ");
        sb.append(" 	AND a.`到店时间` <> '00:00:00' ");
        sb.append(" 	AND a.`到店时间` <> '01:00:00' ");
        sb.append(" 	AND a.`到店时间` <> '03:00:00' ");
        sb.append(" 	GROUP BY a.`顾客编号` ");
        sb.append(" ) c ON a.custId = c.`顾客编号` ");
        sb.append(" LEFT JOIN `lsh车型` as d ON IF(a.intentCarStyle = '', b.`意向车型A`, a.intentCarStyle) = d.`车型编号`  ");
        sb.append(" LEFT JOIN `用户` as e ON e.`用户名` = IF(a.stage <> '01',b.`所属销售顾问`,t.`销售顾问`)  ");

        sb.append(" union all ");

        sb.append(" select  ");
        sb.append(" 	crm_history.id, ");
        sb.append(" 	crm_history.stage, ");
        sb.append(" 	CASE crm_history.stage ");
        sb.append(" 	WHEN '01' THEN '首次离店调研' ");
        sb.append(" 	WHEN '02' THEN '休眠调研' ");
        sb.append(" 	WHEN '03' THEN '战败调研' ");
        sb.append(" 	WHEN '04' THEN 'NPS调研' ");
        sb.append(" 	END stageName, ");
        sb.append(" 	CASE crm_history.surveyResult");
        sb.append(" 	WHEN '1' THEN '完成问卷' ");
        sb.append(" 	WHEN '2' THEN '待跟进' ");
        sb.append(" 	WHEN '3' THEN '无效电话' ");
        sb.append(" 	WHEN '4' THEN '拒绝回访' ");
        sb.append(" 	WHEN '5' THEN '重新分配' ");
        sb.append(" 	WHEN '6' THEN '投诉' ");
        sb.append(" 	WHEN '7' THEN '激活并分配' ");
        sb.append(" 	WHEN '8' THEN '推荐名单推送' ");
        sb.append(" 	ELSE '' END surveyResult, ");
        sb.append(" 	CASE crm_history.valid WHEN '0' THEN '无效' WHEN '1' THEN '有效' ELSE '' END valid, ");
        sb.append(" 	crm_history.custId, ");
        sb.append(" 	`顾客记录汇总`.`顾客姓名` cusName, ");
        sb.append(" 	case when `顾客记录汇总`.`顾客性别` = '男' then '先生' ");
        sb.append(" 	    when `顾客记录汇总`.`顾客性别` = '女' then '女士' ");
        sb.append(" 	else '' end cusAppellation, ");
        sb.append(" 	`顾客记录汇总`.`电话号码` cusPhone, ");
        sb.append(" 	IF(crm_history.stage = '01', `顾客进店记录表`.进店日期, c.firstStoreDate) firstStoreDate, ");
        sb.append(" 	IF(`lsh车型`.`来源` = 'PbP', CONCAT('PbP ', `lsh车型`.`级别`), `lsh车型`.`级别`) intentCarVehicles, ");
        sb.append(" 	`lsh车型`.`车型` intentCarStyle, ");
        sb.append(" 	`顾客记录汇总`.`所属销售顾问` scCode, ");
        sb.append(" 	`用户`.`人员姓名` scName, ");
        sb.append(" 	datediff(CURDATE(),crm_history.connectDate) orderIntervalDays, ");
        sb.append(" 	crm_history.connectDate, ");
        sb.append(" 	crm_history.connectTime, ");
        sb.append(" 	crm_history.nextConnectDate, ");
        sb.append(" 	crm_history.serialNumber ");
        sb.append(" from crm_history as crm_history ");
        sb.append(" JOIN `顾客记录汇总` as `顾客记录汇总` ON crm_history.custId = `顾客记录汇总`.`顾客编号` ");
        sb.append(" LEFT JOIN `顾客进店记录表` as `顾客进店记录表` ON (`顾客进店记录表`.`进店编号` = crm_history.serialNumber AND crm_history.stage = '01') ");
        sb.append(" LEFT JOIN `顾客订单记录` as `顾客订单记录` ON (`顾客订单记录`.`订单编号` = crm_history.serialNumber AND crm_history.stage = '04') ");
        sb.append(" LEFT JOIN `lsh车型` as `lsh车型` ON `lsh车型`.`车型编号` = IF(crm_history.stage <> '01', IF(crm_history.stage <> '04',`顾客记录汇总`.`意向车型A`, `顾客订单记录`.车型), `顾客进店记录表`.`意向车型A`) ");
        sb.append(" LEFT JOIN `用户` AS `用户` ON `用户`.`用户名` = IF(crm_history.stage <> '01',`顾客记录汇总`.`所属销售顾问`,`顾客进店记录表`.`销售顾问`) ");
        sb.append(" LEFT JOIN ( ");
        sb.append(" 	SELECT a.`顾客编号`,MIN(a.`进店日期`) firstStoreDate FROM `顾客进店记录表` a ");
        sb.append(" 	WHERE a.`许可` = :permit ");
        sb.append(" 	AND a.`顾客编号` IS NOT NULL ");
        sb.append(" 	AND a.`是否首次到店` = '是' ");
        sb.append(" 	GROUP BY a.`顾客编号` ");
        sb.append(" ) c ON crm_history.custId = c.`顾客编号` ");
        sb.append(" where crm_history.permit = :permit ");
        sb.append(" 	AND crm_history.updateAccount = :username ");

        sb.append(" ) queryResult where 1 = 1 ");

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("username", username);
        params.put("firstToShopDate", firstToShopDate);
        params.put("NPSDate", NPSDate);
        if (stage != null && !"".equals(stage) && !"00".equals(stage)) {
            sb.append(" 	AND queryResult.stage = :stage ");
            params.put("stage", stage);
        }
        if (surveyResult != null && !"".equals(surveyResult)) {
            sb.append(" 	AND queryResult.surveyResult = :surveyResult ");
            params.put("surveyResult", surveyResult);
        }
        if (connectStartDate != null && !"".equals(connectStartDate)) {
            sb.append(" 	AND queryResult.connectDate >= :connectStartDate  ");
            try {
                params.put("connectStartDate", new Date(sdf.parse(connectStartDate).getTime()));
            } catch (ParseException e) {
                logger.error("调研开始日期格式不正确", e);
            }
        }
        if (connectEndDate != null && !"".equals(connectEndDate)) {
            sb.append(" 	AND queryResult.connectDate <= :connectEndDate ");
            try {
                params.put("connectEndDate", new Date(sdf.parse(connectEndDate).getTime()));
            } catch (ParseException e) {
                logger.error("调研结束日期格式不正确", e);
            }
        }
        if (nextConnectStartDate != null && !"".equals(nextConnectStartDate)) {
            sb.append(" 	AND DATE_FORMAT(queryResult.nextConnectDate,'%Y-%m-%d') >= :nextConnectStartDate ");
            try {
                params.put("nextConnectStartDate", new Date(sdf.parse(nextConnectStartDate).getTime()));
            } catch (ParseException e) {
                logger.error("下次调研开始日期格式不正确", e);
            }
        }
        if (nextConnectEndDate != null && !"".equals(nextConnectEndDate)) {
            sb.append(" 	AND DATE_FORMAT(queryResult.nextConnectDate,'%Y-%m-%d') <= :nextConnectEndDate ");
            try {
                params.put("nextConnectEndDate", new Date(sdf.parse(nextConnectEndDate).getTime()));
            } catch (ParseException e) {
                logger.error("下次调研结束日期格式不正确", e);
            }
        }
        if (cusName != null && !"".equals(cusName)) {
            sb.append(" 	AND queryResult.cusName LIKE :cusName ");
            params.put("cusName", "%" + cusName + "%");
        }
        if (cusPhone != null && !"".equals(cusPhone)) {
            sb.append(" 	AND queryResult.cusPhone like :cusPhone ");
            params.put("cusPhone", "%" + cusPhone + "%");
        }
        if (intentCarVehicles != null && !"".equals(intentCarVehicles)) {
            sb.append(" 	AND queryResult.intentCarVehicles = :intentCarVehicles ");
            params.put("intentCarVehicles", intentCarVehicles);
        }
        if (intentCarStyle != null && !"".equals(intentCarStyle)) {
            sb.append(" 	AND queryResult.intentCarStyle = :intentCarStyle ");
            params.put("intentCarStyle", intentCarStyle);
        }
          return this.getPage(sb.toString(), params, pageSize, pageIndex);
    }

    @Override
    public List findAnswerByTopicId(String topicId, String historyId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	crm_answer.id, ");
        sb.append(" 	crm_answer.stem_id, ");
        sb.append(" 	crm_answer.response, ");
        sb.append(" 	crm_answer.forward_ending, ");
        sb.append(" 	CASE WHEN crm_flow.answer_id IS NULL THEN '' ");
        sb.append(" 	ELSE 'Y' END selectresult ");
        sb.append(" from crm_answer AS crm_answer ");
        sb.append(" LEFT JOIN ( ");
        sb.append(" 		select crm_flow.topic_id,crm_flow.answer_id from crm_flow AS crm_flow ");
        sb.append(" 		WHERE crm_flow.history_id = :historyId AND crm_flow.topic_id = :topicId ");
        sb.append(" 	) AS crm_flow ON crm_answer.id = crm_flow.answer_id ");
        sb.append(" WHERE crm_answer.stem_id = :topicId order by crm_answer.id ");
        Map params = new HashMap();
        params.put("historyId", historyId);
        params.put("topicId", topicId);
        return this.getList(sb.toString(), params);
    }

    @Override
    public PageData findNowAndOtherAboutSurvey(String permit, String username, RequestBase requestBase) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	crm_history.id, ");
        sb.append(" 	crm_history.stage, ");
        sb.append(" 	CASE crm_history.stage ");
        sb.append(" 	WHEN '01' THEN '首次离店调研' ");
        sb.append(" 	WHEN '02' THEN '休眠调研' ");
        sb.append(" 	WHEN '03' THEN '战败调研' ");
        sb.append(" 	WHEN '04' THEN 'NPS调研' ");
        sb.append(" 	ELSE '' END stageName, ");
        sb.append(" 	CASE crm_history.surveyResult");
        sb.append(" 	WHEN '1' THEN '完成问卷' ");
        sb.append(" 	WHEN '2' THEN '待跟进' ");
        sb.append(" 	WHEN '3' THEN '无效电话' ");
        sb.append(" 	WHEN '4' THEN '拒绝回访' ");
        sb.append(" 	WHEN '5' THEN '重新分配' ");
        sb.append(" 	WHEN '6' THEN '投诉' ");
        sb.append(" 	WHEN '7' THEN '激活并分配' ");
        sb.append(" 	WHEN '8' THEN '推荐名单推送' ");
        sb.append(" 	ELSE '' END surveyResult, ");
        sb.append(" 	CASE crm_history.valid ");
        sb.append(" 	WHEN '0' THEN '无效' ");
        sb.append(" 	WHEN '1' THEN '有效' ");
        sb.append(" 	ELSE '' END valid, ");
        sb.append(" 	crm_history.custId, ");
        sb.append(" 	`顾客记录汇总`.`顾客姓名` cusName, ");
        sb.append(" 	`顾客记录汇总`.`电话号码` cusPhone, ");
        sb.append(" 	crm_history.connectDate, ");
        sb.append(" 	crm_history.nextConnectDate AS nextConnectTime, ");
        sb.append(" 	DATE_FORMAT(crm_history.nextConnectDate,'%Y-%m-%d') nextConnectDate, ");
        sb.append(" 	crm_history.serialNumber, ");
        sb.append(" 	(select a.remark from crm_history_log a where a.history_id = crm_history.id ORDER BY a.updateTime desc limit 1) remark ");
        sb.append(" from crm_history AS crm_history ");
        sb.append(" JOIN `顾客记录汇总` as `顾客记录汇总` ON `顾客记录汇总`.`顾客编号` = crm_history.custId ");
        sb.append(" where crm_history.permit = :permit ");
        sb.append(" AND crm_history.crm = :username ");
        sb.append(" AND DATE_FORMAT(crm_history.nextConnectDate,'%Y-%m-%d') = CURDATE()  ");
        sb.append(" AND crm_history.surveyResult = '2' ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("username", username);
        return this.getPage(sb.toString(), params, requestBase.getPageSize(), requestBase.getPageIndex());
    }

    @Override
    public PageData findNowAboutSurveyInTenMinutes(String permit, String username, Timestamp nextConnectTimeStart, Timestamp nextConnectTimeEnd, RequestBase requestBase) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	crm_history.id, ");
        sb.append(" 	crm_history.stage, ");
        sb.append(" 	CASE crm_history.stage ");
        sb.append(" 	WHEN '01' THEN '首次离店调研' ");
        sb.append(" 	WHEN '02' THEN '休眠调研' ");
        sb.append(" 	WHEN '03' THEN '战败调研' ");
        sb.append(" 	WHEN '04' THEN 'NPS调研' ");
        sb.append(" 	ELSE '' END stageName, ");
        sb.append(" 	CASE crm_history.surveyResult");
        sb.append(" 	WHEN '1' THEN '完成问卷' ");
        sb.append(" 	WHEN '2' THEN '待跟进' ");
        sb.append(" 	WHEN '3' THEN '无效电话' ");
        sb.append(" 	WHEN '4' THEN '拒绝回访' ");
        sb.append(" 	WHEN '5' THEN '重新分配' ");
        sb.append(" 	WHEN '6' THEN '投诉' ");
        sb.append(" 	WHEN '7' THEN '激活并分配' ");
        sb.append(" 	WHEN '8' THEN '推荐名单推送' ");
        sb.append(" 	ELSE '' END surveyResult, ");
        sb.append(" 	CASE crm_history.valid ");
        sb.append(" 	WHEN '0' THEN '无效' ");
        sb.append(" 	WHEN '1' THEN '有效' ");
        sb.append(" 	ELSE '' END valid, ");
        sb.append(" 	crm_history.custId, ");
        sb.append(" 	`顾客记录汇总`.`顾客姓名` cusName, ");
        sb.append(" 	`顾客记录汇总`.`电话号码` cusPhone, ");
        sb.append(" 	crm_history.connectDate, ");
        sb.append(" 	crm_history.nextConnectDate AS nextConnectTime, ");
        sb.append(" 	DATE_FORMAT(crm_history.nextConnectDate,'%Y-%m-%d') nextConnectDate, ");
        sb.append(" 	crm_history.serialNumber, ");
        sb.append(" 	crm_history.remark ");
        sb.append(" from crm_history as crm_history ");
        sb.append(" JOIN `顾客记录汇总` as `顾客记录汇总` ON `顾客记录汇总`.`顾客编号` = crm_history.custId ");
        sb.append(" WHERE crm_history.permit = :permit ");
        sb.append(" AND crm_history.crm = :username ");
        sb.append(" AND crm_history.surveyResult = '2' ");
        sb.append(" AND crm_history.nextConnectDate >= :nextConnectTimeStart ");
        sb.append(" AND crm_history.nextConnectDate <= :nextConnectTimeEnd ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("username", username);
        params.put("nextConnectTimeStart", nextConnectTimeStart);
        params.put("nextConnectTimeEnd", nextConnectTimeEnd);
        return this.getPage(sb.toString(), params, requestBase.getPageSize(), requestBase.getPageIndex());
    }

    @Override
    public List findVersionForDealerAboutHistory(String permit, String connectDateStart, String connectDateEnd) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	crm_history.dealerVersion as version ");
        sb.append(" from crm_history AS crm_history ");
        sb.append(" where crm_history.permit = :permit ");
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            sb.append(" 	AND crm_history.connectDate >= :connectDateStart ");
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            sb.append(" 	AND crm_history.connectDate <= :connectDateEnd ");
        }
        sb.append(" GROUP BY crm_history.dealerVersion ");
        Map params = new HashMap();
        params.put("permit", permit);
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            params.put("connectDateStart", connectDateStart);
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            params.put("connectDateEnd", connectDateEnd);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public List findVersionForGroupAboutHistory(String permit, String version, String connectDateStart, String connectDateEnd) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select crm_history.groupVersion from crm_history as crm_history ");
        sb.append(" WHERE crm_history.permit = :permit ");
        if (version == null || "".equals(version)) {
            sb.append("     AND crm_history.dealerVersion is null ");
        } else {
            sb.append("     AND crm_history.dealerVersion = :version ");
        }
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            sb.append(" 	AND crm_history.connectDate >= :connectDateStart ");
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            sb.append(" 	AND crm_history.connectDate <= :connectDateEnd ");
        }
        sb.append(" GROUP BY crm_history.groupVersion ");
        Map params = new HashMap();
        params.put("permit", permit);
        if (version != null && !"".equals(version)) {
            params.put("version", version);
        }
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            params.put("connectDateStart", connectDateStart);
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            params.put("connectDateEnd", connectDateEnd);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public List findSurveyResult(String permit, String version, String connectDateStart, String connectDateEnd) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	`顾客记录汇总`.`顾客姓名` cusName, ");
        sb.append(" 	`顾客记录汇总`.`电话号码` cusPhone, ");
        sb.append(" 	`用户`.`人员姓名` scName, ");
        sb.append(" 	crm_history.groupVersion, ");
        sb.append(" 	crm_history.dealerVersion, ");
        sb.append(" 	crm_history.id, ");
        sb.append(" 	GROUP_CONCAT(IFNULL(crm_flow.topic_id,'') SEPARATOR '___MARK___') topicId, ");
        sb.append(" 	GROUP_CONCAT(IFNULL(crm_flow.crm_remark,'') SEPARATOR '___MARK___') crm_remark, ");
        sb.append(" 	GROUP_CONCAT(IFNULL(crm_answer.response,'') SEPARATOR '___MARK___') answerId, ");
        sb.append(" 	GROUP_CONCAT(IFNULL(crm_answer.forward_ending,'') SEPARATOR '___MARK___') forwardEnding ");
        sb.append(" from crm_history AS crm_history ");
        sb.append(" JOIN `顾客记录汇总` AS `顾客记录汇总` ON `顾客记录汇总`.`顾客编号` = crm_history.custId ");
        sb.append(" JOIN crm_flow AS crm_flow ON crm_flow.history_id = crm_history.id ");
        sb.append(" left JOIN crm_answer AS crm_answer ON crm_answer.id = crm_flow.answer_id ");
        sb.append(" left JOIN `用户` AS `用户` ON `顾客记录汇总`.`所属销售顾问` = `用户`.用户名 ");
        sb.append(" where crm_history.permit = :permit ");
        if (version == null || "".equals(version)) {
            sb.append(" AND crm_history.dealerVersion is null ");
        } else {
            sb.append(" AND crm_history.dealerVersion = :version ");
        }
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            sb.append(" 	AND crm_history.connectDate >= :connectDateStart ");
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            sb.append(" 	AND crm_history.connectDate <= :connectDateEnd ");
        }
        sb.append(" GROUP BY crm_history.id ");
        Map params = new HashMap();
        if (version != null && !"".equals(version)) {
            params.put("version", version);
        }
        params.put("permit", permit);
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            params.put("connectDateStart", connectDateStart);
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            params.put("connectDateEnd", connectDateEnd);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public List findVersionForGroupAboutHistory(String[] areas, String connectDateStart, String connectDateEnd) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select crm_history.groupVersion from crm_history AS crm_history ");
        sb.append(" where 1 = 1 ");
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            sb.append(" 	AND crm_history.connectDate >= :connectDateStart ");
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            sb.append(" 	AND crm_history.connectDate <= :connectDateEnd ");
        }
        if (areas != null) {
            sb.append(" 	AND crm_history.permit IN ( ");
            sb.append(" 		select `lsh经销商表`.许可_BMBS from `lsh经销商表` AS `lsh经销商表` ");
            sb.append(" 		WHERE `lsh经销商表`.大区 IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append(" 			OR `lsh经销商表`.小区 IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append(" 			OR `lsh经销商表`.CTA IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append(" 			OR `lsh经销商表`.许可_BMBS IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append(" 	) ");
        }
        sb.append(" GROUP BY crm_history.groupVersion ");

        Map params = new HashMap();
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            params.put("connectDateStart", connectDateStart);
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            params.put("connectDateEnd", connectDateEnd);
        }
        if (areas != null) {
            for (int i = 0; i < areas.length; i++) {
                params.put("area" + i, areas[i]);
            }
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public List findSurveyResultForGroup(String[] areas, String version, String connectDateStart, String connectDateEnd) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	`顾客记录汇总`.`顾客姓名` cusName, ");
        sb.append(" 	`顾客记录汇总`.`电话号码` cusPhone, ");
        sb.append(" 	crm_history.groupVersion, ");
        sb.append(" 	crm_history.dealerVersion, ");
        sb.append(" 	crm_history.id, ");
        sb.append(" 	GROUP_CONCAT(crm_flow.topic_id SEPARATOR '___MARK___') topicId, ");
        sb.append(" 	GROUP_CONCAT(IFNULL(crm_flow.crm_remark,' ') SEPARATOR '___MARK___') crm_remark, ");
        sb.append(" 	GROUP_CONCAT(ifnull(crm_answer.response, ' ') SEPARATOR '___MARK___') answerId, ");
        sb.append(" 	GROUP_CONCAT(ifnull(crm_answer.forward_ending, ' ') SEPARATOR '___MARK___') forwardEnding ");
        sb.append(" from crm_history AS crm_history ");
        sb.append(" JOIN `顾客记录汇总` AS `顾客记录汇总` ON `顾客记录汇总`.`顾客编号` = crm_history.custId ");
        sb.append(" JOIN crm_flow AS crm_flow ON crm_flow.history_id = crm_history.id ");
        sb.append(" left JOIN crm_answer AS crm_answer ON crm_answer.id = crm_flow.answer_id ");
        sb.append(" where crm_history.groupVersion = :version ");
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            sb.append(" 	AND crm_history.connectDate >= :connectDateStart ");
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            sb.append(" 	AND crm_history.connectDate <= :connectDateEnd ");
        }
        if (areas != null) {
            sb.append(" 	AND crm_history.permit in ( ");
            sb.append("         select `lsh经销商表`.许可_BMBS from `lsh经销商表` AS `lsh经销商表` ");
            sb.append(" 		WHERE `lsh经销商表`.大区 IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append(" 			OR `lsh经销商表`.小区 IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append(" 			OR `lsh经销商表`.CTA IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append(" 			OR `lsh经销商表`.许可_BMBS IN (");
            for (int i = 0; i < areas.length; i++) {
                sb.append("         :area" + i);
                if (i < areas.length - 1) {
                    sb.append("     , ");
                }
            }
            sb.append("             ) ");
            sb.append("         ) ");
        }
        sb.append(" GROUP BY crm_history.id ");
        Map params = new HashMap();
        params.put("version", version);
        if (areas != null) {
            for (int i = 0; i < areas.length; i++) {
                params.put("area" + i, areas[i]);
            }
        }
        if (connectDateStart != null && !"".equals(connectDateStart)) {
            params.put("connectDateStart", connectDateStart);
        }
        if (connectDateEnd != null && !"".equals(connectDateEnd)) {
            params.put("connectDateEnd", connectDateEnd);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public PageData findLiVEClue(String permit, String createDateStart, String createDateEnd, String lastFollowDateStart,
                                 String lastFollowDateEnd, String state, String intentCarLevel, String intentCarStyle, int pageSize, int pageIndex) {
        StringBuffer sb = new StringBuffer();
        Map params = new HashMap();
        sb.append(" SELECT   ");
        sb.append(" 	`顾客记录汇总`.`姓名`,      ");
        sb.append(" 	`顾客记录汇总`.`称谓`,     ");
        sb.append(" 	`顾客记录汇总`.`联系电话` 联系电话,     ");
        sb.append(" 	`报价客户表`.`客户地址`,     ");
        sb.append(" 	IF(`顾客进店记录表`.`顾客编号` IS NULL,'未进店','已进店') 状态,      ");
        sb.append(" 	`顾客记录汇总`.`意向级别`,     ");
        sb.append(" 	`顾客记录汇总`.`首次渠道来源`,      ");
        sb.append(" 	`lsh市场设置`.`来源分类` 首次渠道类型,      ");
        sb.append(" 	`顾客记录汇总`.`首次渠道`,      ");
        sb.append(" 	`顾客记录汇总`.`市场活动`,      ");
        sb.append(" 	'未关闭' lead状态,      ");
        sb.append(" 	car_sales_state.`value` 销售状态,      ");
        sb.append(" 	`顾客记录汇总`.`创建日期`,      ");
        sb.append(" 	`用户1`.`人员姓名` 创建人,      ");
        sb.append(" 	`用户2`.`人员姓名` 当前用户,      ");
        sb.append(" 	`用户2`.`所属部门` 当前用户部门,      ");
        sb.append(" 	`销售顾问分组`.`组名` 当前用户销售组,     ");
        sb.append(" 	`用户1`.`人员姓名` 原所属销售顾问,      ");
        sb.append(" 	`lsh经销商表`.`经销商名` 经销商,      ");
        sb.append(" 	`跟进记录表`.`跟进日期` 上次跟进日期,      ");
        sb.append(" 	`sc_call_history`.`上次跟进通话时长`,     ");
        sb.append(" 	`顾客进店记录表`.`首次进店日期`,     ");
        sb.append(" 	`顾客进店记录表`.`上次进店日期`,      ");
        sb.append(" 	`跟进记录表`.`销售顾问跟进总次数`,     ");
        sb.append(" 	lsh车型.`级别` 意向车系,      ");
        sb.append(" 	lsh车型.`车型` 意向车型,      ");
        sb.append(" 	`顾客试驾记录`.`首次试驾日期`,     ");
        sb.append(" 	`顾客试驾记录`.`最近一次试驾日期`,      ");
        sb.append(" 	`顾客试驾记录`.`最近一次试驾车型`,      ");
        sb.append(" 	IF(`报价单表`.`报价日期` IS NULL,'否','是') 是否报价,      ");
        sb.append(" 	`报价单表`.`报价日期`,     ");
        sb.append(" 	IF(`报价单表`.`订单编号` IS NULL,'否','是') 是否订单,      ");
        sb.append(" 	`报价单表`.`订单客户姓名`,      ");
        sb.append(" 	`报价单表`.`订单日期`,      ");
        sb.append(" 	`报价单表`.`订单车辆VIN码`,      ");
        sb.append(" 	`报价单表`.`是否零售`,      ");
        sb.append(" 	`报价单表`.`零售日期`,      ");
        sb.append(" 	`报价单表`.`零售客户姓名`,      ");
        sb.append(" 	`报价单表`.`零售类型`,      ");
        sb.append(" 	`报价单表`.`是否保险`,      ");
        sb.append(" 	`报价单表`.`是否金融`,      ");
        sb.append(" 	`报价单表`.`是否交车`,      ");
        sb.append(" 	`报价单表`.`交车日期`,      ");
        sb.append(" 	`报价单表`.`售后联系人1`,      ");
        sb.append(" 	`报价单表`.`售后联系人电话`,      ");
        sb.append(" 	`报价单表`.`售后联系人微信`,      ");
        sb.append(" 	`报价单表`.`售后联系人地址`,     ");
        sb.append(" 	`顾客记录汇总`.`保有品牌`,     ");
        sb.append(" 	`顾客记录汇总`.`保有车型`,    ");
        sb.append(" 	IF(`跟进记录表`.`跟进结果` = '新车战败',`跟进记录表`.`跟进日期`,'') 战败日期,     ");
        sb.append(" 	IF(`跟进记录表`.`跟进结果` = '新车战败',`跟进记录表`.`战败原因`,'') 战败原因,     ");
        sb.append(" 	' ' 激活日期  ");
        sb.append("  FROM (  ");
        sb.append(" 	SELECT   ");
        sb.append(" 		`lsh导入名单`.`许可`,  ");
        sb.append(" 		'' 顾客编号,  ");
        sb.append(" 		`lsh导入名单`.`顾客姓名` 姓名,       ");
        sb.append(" 		CASE `lsh导入名单`.`顾客性别`      ");
        sb.append(" 			WHEN '男' THEN '先生'     ");
        sb.append(" 			WHEN '女' THEN '女士'     ");
        sb.append(" 		ELSE '' END '称谓',     ");
        sb.append(" 		`lsh导入名单`.`电话号码` 联系电话,      ");
        sb.append(" 		`lsh导入名单`.`客户级别` 意向级别,     ");
        sb.append(" 		`lsh导入名单`.`渠道` 首次渠道,       ");
        sb.append(" 		`lsh导入名单`.`来源` 首次渠道来源,   ");
        sb.append(" 		'' 顾客渠道,  ");
        sb.append(" 		`lsh活动表`.`活动名称` 市场活动,    ");
        sb.append(" 		`lsh导入名单`.`新车销售状态` 销售状态,      ");
        sb.append(" 		`lsh导入名单`.`导入日期` 创建日期,  ");
        sb.append(" 		`lsh导入名单`.`所属CDO` 销售顾问,  ");
        sb.append(" 		`lsh导入名单`.`所属销售顾问`,  ");
        sb.append(" 		`lsh导入名单`.`车型编号`,  ");
        sb.append(" 		'否' `是否订单`,  ");
        sb.append(" 		'否' `是否报价`,  ");
        sb.append(" 		`lsh导入名单`.`保有品牌`,     ");
        sb.append(" 		' ' `保有车型`   ");
        sb.append(" 	FROM (         ");
        sb.append(" 		SELECT *  ");
        sb.append(" 		FROM `lsh导入名单`   ");
        sb.append(" 		WHERE `lsh导入名单`.`许可` = :permit  ");
        sb.append(" 		AND `lsh导入名单`.`是否有效线索` = '是'  ");
        sb.append(" 		AND `lsh导入名单`.`是否线索` = '是'  ");
        sb.append(" 		AND `lsh导入名单`.`导入日期` >= date_sub(curdate(), INTERVAL 3 MONTH)  ");
        sb.append(" 		AND `lsh导入名单`.`电话号码` NOT in (SELECT `顾客记录汇总`.`电话号码` FROM `顾客记录汇总` WHERE `顾客记录汇总`.`许可` = `lsh导入名单`.`许可`)  ");
        sb.append(" 		GROUP BY `lsh导入名单`.`电话号码`  ");
        sb.append(" 		ORDER BY `lsh导入名单`.`线索创建日期` DESC  ");
        sb.append(" 	) `lsh导入名单`     ");
        sb.append(" 	LEFT JOIN `lsh活动表` AS `lsh活动表` ON `lsh导入名单`.`活动编号` = `lsh活动表`.`活动编号`   ");
        sb.append(" 	UNION ALL  ");
        sb.append(" 	SELECT  ");
        sb.append(" 		`顾客记录汇总`.`许可`,  ");
        sb.append(" 		`顾客记录汇总`.`顾客编号`,  ");
        sb.append(" 		`顾客记录汇总`.`顾客姓名` 姓名,      ");
        sb.append(" 		CASE `顾客记录汇总`.`顾客性别`      ");
        sb.append(" 			WHEN '男' THEN '先生'     ");
        sb.append(" 			WHEN '女' THEN '女士'     ");
        sb.append(" 		ELSE '' END '称谓',     ");
        sb.append(" 		`顾客记录汇总`.`电话号码` 联系电话,      ");
        sb.append(" 		`顾客记录汇总`.`新顾客级别` 意向级别,    ");
        sb.append(" 		`顾客记录汇总`.`顾客首次渠道` 首次渠道,    ");
        sb.append(" 		`顾客记录汇总`.`顾客来源` 首次渠道来源,     ");
        sb.append(" 		`顾客记录汇总`.`顾客渠道`,  ");
        sb.append(" 		`顾客记录汇总`.`市场活动邀约` 市场活动,  ");
        sb.append(" 		`顾客记录汇总`.`新车销售状态` 销售状态,      ");
        sb.append(" 		`顾客记录汇总`.`留档日期` 创建日期,  ");
        sb.append(" 		IF(顾客记录汇总.`顾客渠道` = '散客', `顾客记录汇总`.`销售顾问`, IFNULL(b.`所属CDO`, `顾客记录汇总`.`销售顾问`)) 销售顾问,  ");
        sb.append(" 		`顾客记录汇总`.`所属销售顾问`,  ");
        sb.append(" 		`顾客记录汇总`.`意向车型A` 车型编号,  ");
        sb.append(" 		`顾客记录汇总`.`是否订单`,    ");
        sb.append(" 		`顾客记录汇总`.`是否报价`,   ");
        sb.append(" 		`顾客记录汇总`.`保有品牌`,     ");
        sb.append(" 		`顾客记录汇总`.`保有车型`         ");
        sb.append(" 	 ");
        sb.append(" 	FROM `顾客记录汇总`   ");
        sb.append("     LEFT JOIN ( ");
        sb.append(" 	    SELECT a.电话号码,a.`所属CDO` from  lsh导入名单 a ");
        sb.append(" 	    JOIN ( ");
        sb.append(" 		    SELECT MAX(a.`导入编号`) 导入编号 FROM `lsh导入名单` a ");
        sb.append(" 			    WHERE a.`许可` = :permit ");
        sb.append(" 			    AND a.`是否有效线索` = '是' ");
        sb.append(" 			    AND a.`是否线索` = '是' ");
        sb.append(" 		    GROUP BY a.`电话号码` ");
        sb.append(" 	    ) b ON a.导入编号 = b.导入编号 ");
        sb.append("     ) b ON 顾客记录汇总.`电话号码` = b.电话号码 ");
        sb.append(" 	WHERE `顾客记录汇总`.`许可` = :permit      ");
        sb.append("  ) `顾客记录汇总`  ");
        sb.append("  JOIN `lsh经销商表` AS `lsh经销商表` ON `lsh经销商表`.`许可_BMBS` = `顾客记录汇总`.`许可`     ");
        sb.append("  LEFT JOIN car_sales_state ON car_sales_state.`key` = `顾客记录汇总`.`销售状态`   ");
        sb.append("  LEFT JOIN lsh车型 AS lsh车型 ON lsh车型.`车型编号` = `顾客记录汇总`.`车型编号`      ");
        sb.append("  LEFT JOIN `用户` AS `用户1` ON `用户1`.`用户名` = `顾客记录汇总`.`销售顾问`      ");
        sb.append("  LEFT JOIN `用户` AS `用户2` ON `用户2`.`用户名` = `顾客记录汇总`.`所属销售顾问`    ");
        sb.append("  LEFT JOIN (   ");
        sb.append(" 	SELECT    ");
        sb.append(" 		lsh市场设置.`渠道`,   ");
        sb.append(" 		lsh市场设置.`来源`,   ");
        sb.append(" 		lsh市场设置.`来源分类`   ");
        sb.append(" 	FROM lsh市场设置   ");
        sb.append(" 	GROUP BY lsh市场设置.`渠道`,lsh市场设置.`来源`   ");
        sb.append("  ) AS `lsh市场设置` ON `lsh市场设置`.`来源` = `顾客记录汇总`.`首次渠道来源` AND `lsh市场设置`.`渠道` = `顾客记录汇总`.`顾客渠道`      ");
        sb.append("  LEFT JOIN (      ");
        sb.append(" 	SELECT      ");
        sb.append(" 		`顾客进店记录表`.`许可`,     ");
        sb.append(" 		`顾客进店记录表`.`顾客编号`,     ");
        sb.append(" 		IF(     ");
        sb.append(" 			MIN(`顾客进店记录表`.`进店日期`) = '' OR MIN(`顾客进店记录表`.`进店日期`) IS NULL,     ");
        sb.append(" 			MIN(`顾客进店记录表`.`进店日期`),     ");
        sb.append(" 			date_FORMAT(MIN(`顾客进店记录表`.`进店日期`),'%Y-%m-%d %H:%i')     ");
        sb.append(" 		) 首次进店日期  ,     ");
        sb.append(" 		IF(     ");
        sb.append(" 			MAX(`顾客进店记录表`.`进店日期`) IS NULL OR MAX(`顾客进店记录表`.`进店日期`) = '',     ");
        sb.append(" 			MAX(`顾客进店记录表`.`进店日期`),     ");
        sb.append(" 			DATE_FORMAT(MAX(`顾客进店记录表`.`进店日期`),'%Y-%m-%d %H:%i')     ");
        sb.append(" 		) 上次进店日期     ");
        sb.append(" 	from (     ");
        sb.append(" 		select      ");
        sb.append(" 				`顾客进店记录表`.`许可`,     ");
        sb.append(" 				`顾客进店记录表`.`顾客编号`,     ");
        sb.append(" 				CONCAT(`顾客进店记录表`.`进店日期`,' ',`顾客进店记录表`.`到店时间`) 进店日期     ");
        sb.append(" 			from `顾客进店记录表`      ");
        sb.append(" 			WHERE `顾客进店记录表`.`是否首次到店` = '是'      ");
        sb.append(" 			AND `顾客进店记录表`.`许可` = :permit    ");
        sb.append(" 		) AS `顾客进店记录表`     ");
        sb.append(" 		GROUP BY `顾客进店记录表`.`许可`,`顾客进店记录表`.`顾客编号`      ");
        sb.append("  ) `顾客进店记录表` ON `顾客进店记录表`.`顾客编号` = `顾客记录汇总`.`顾客编号`      ");
        sb.append("  LEFT JOIN (      ");
        sb.append(" 	SELECT ");
        sb.append(" 		a.`顾客编号`, ");
        sb.append(" 		a.`首次试驾日期`, ");
        sb.append(" 		a.`最近一次试驾日期`, ");
        sb.append(" 		b.`车型` `最近一次试驾车型` ");
        sb.append(" 	from ( ");
        sb.append(" 		select ");
        sb.append(" 			`顾客进店记录表`.`顾客编号`, ");
        sb.append(" 			DATE_FORMAT(MIN(顾客试驾记录.`更新时间`),'%Y-%m-%d') 首次试驾日期, ");
        sb.append(" 			DATE_FORMAT(MAX(顾客试驾记录.`更新时间`),'%Y-%m-%d') 最近一次试驾日期 ");
        sb.append(" 		from 顾客试驾记录 ");
        sb.append(" 		JOIN `顾客进店记录表` ON 顾客试驾记录.`进店编号` = `顾客进店记录表`.`进店编号` ");
        sb.append(" 		WHERE `顾客进店记录表`.`顾客编号` IS NOT NULL ");
        sb.append(" 		GROUP BY 顾客进店记录表.`顾客编号` ");
        sb.append(" 	) a ");
        sb.append(" 	JOIN ( ");
        sb.append(" 		SELECT ");
        sb.append(" 			a.`顾客编号`, ");
        sb.append(" 			b.`车型` ");
        sb.append(" 		FROM ( ");
        sb.append(" 			select ");
        sb.append(" 				`顾客进店记录表`.`顾客编号`, ");
        sb.append(" 				MAX(顾客试驾记录.`更新时间`) 更新时间 ");
        sb.append(" 			from 顾客试驾记录 ");
        sb.append(" 			JOIN `顾客进店记录表` ON 顾客试驾记录.`进店编号` = `顾客进店记录表`.`进店编号` ");
        sb.append(" 			WHERE `顾客进店记录表`.`顾客编号` IS NOT NULL ");
        sb.append(" 			GROUP BY 顾客进店记录表.`顾客编号` ");
        sb.append(" 		) a ");
        sb.append(" 		JOIN ( ");
        sb.append(" 			select ");
        sb.append(" 				`顾客进店记录表`.`顾客编号`, ");
        sb.append(" 				顾客试驾记录.`更新时间`, ");
        sb.append(" 				lsh车型.`车型` ");
        sb.append(" 			from 顾客试驾记录 ");
        sb.append(" 			JOIN `顾客进店记录表` ON 顾客试驾记录.`进店编号` = `顾客进店记录表`.`进店编号` ");
        sb.append(" 			JOIN lsh车型 ON lsh车型.`车型编号` = 顾客试驾记录.`试驾车型编号` ");
        sb.append(" 			GROUP BY `顾客进店记录表`.`顾客编号`, 顾客试驾记录.`更新时间` ");
        sb.append(" 		) b ON a.`顾客编号` = b.`顾客编号` AND a.`更新时间` = b.`更新时间` ");
        sb.append(" 	) b ON a.`顾客编号` = b.`顾客编号`      ");
        sb.append("  ) `顾客试驾记录` ON `顾客试驾记录`.`顾客编号` = `顾客记录汇总`.`顾客编号`      ");
        sb.append("  LEFT JOIN (      ");
        sb.append(" 	select      ");
        sb.append(" 	`报价单表`.`顾客编号`,      ");
        sb.append(" 	`报价订单表`.`订单编号`,      ");
        sb.append(" 	`报价订单表`.`订单客户姓名`,      ");
        sb.append(" 	`报价订单表`.`订单申请日期` 订单日期,      ");
        sb.append(" 	`报价单表`.`报价日期`,   ");
        sb.append(" 	`lsh库存表`.`车架号` 订单车辆VIN码,      ");
        sb.append(" 	IF(`报价交车表`.`开票时间` IS NULL,'否','是') 是否零售,      ");
        sb.append(" 	IF(`报价交车表`.`开票时间` IS NULL,' ',DATE_FORMAT(`报价交车表`.`开票时间`,'%Y-%m-%d')) 零售日期,      ");
        sb.append(" 	`报价交车表`.`开票客户姓名` 零售客户姓名,      ");
        sb.append(" 	`报价订单表`.`销售类型` 零售类型,      ");
        sb.append(" 	IF(`报价单表`.`保险编号` IS NULL,'否','是') 是否保险,      ");
        sb.append(" 	IF(`报价单表`.`金融` IS NULL,'否','是') 是否金融,      ");
        sb.append(" 	IF(`报价交车表`.`交车日期` IS NULL,'否','是') 是否交车,      ");
        sb.append(" 	`报价交车表`.`交车日期` 交车日期,      ");
        sb.append(" 	`报价交车表`.`售后联系人` 售后联系人1,      ");
        sb.append(" 	`报价交车表`.`售后联系人电话` 售后联系人电话,      ");
        sb.append(" 	`报价交车表`.`售后联系人微信` 售后联系人微信,      ");
        sb.append(" 	`报价交车表`.`售后联系人地址` 售后联系人地址      ");
        sb.append(" 	from (      ");
        sb.append(" 		SELECT       ");
        sb.append(" 			`报价单表`.`顾客编号`,      ");
        sb.append(" 			`报价单表`.`报价编号`,   ");
        sb.append(" 			`报价单表`.`报价时间` 报价日期,          ");
        sb.append(" 			`报价单表`.`订单编号`,      ");
        sb.append(" 			`报价单表`.`交车`,      ");
        sb.append(" 			`报价单表`.`库存编号`,      ");
        sb.append(" 			`报价单表`.`保险编号`,      ");
        sb.append(" 			`报价单表`.`金融`      ");
        sb.append(" 		FROM `报价单表` AS `报价单表`      ");
        sb.append(" 		WHERE `报价单表`.`许可` = :permit       ");
        sb.append(" 		AND `报价单表`.`是否还原点` = '是'      ");
        sb.append(" 		AND `报价单表`.`是否驳回` = '否'      ");
        sb.append(" 	) `报价单表`      ");
        sb.append(" 	LEFT JOIN `lsh库存表` AS `lsh库存表` ON `lsh库存表`.`库存编号` = `报价单表`.`库存编号`      ");
        sb.append(" 	LEFT JOIN `报价订单表` as `报价订单表` on `报价订单表`.`订单编号` = `报价单表`.`订单编号`      ");
        sb.append(" 	LEFT JOIN `报价交车表` AS `报价交车表` ON `报价交车表`.`编号` = `报价单表`.`交车`      ");
        sb.append("  ) `报价单表` ON `顾客记录汇总`.`顾客编号` = `报价单表`.`顾客编号`      ");
        sb.append("  LEFT JOIN (     ");
        sb.append(" 	select `报价客户表`.`电话`,MAX(`报价客户表`.`客户地址`) `客户地址` from (     ");
        sb.append(" 		select      ");
        sb.append(" 			`报价客户表`.`电话`,     ");
        sb.append(" 			CONCAT(`报价客户表`.`省份`,`报价客户表`.`城市`,`报价客户表`.`区域`,`报价客户表`.`详细地址`) 客户地址      ");
        sb.append(" 		from `报价客户表` AS `报价客户表`      ");
        sb.append(" 	) AS `报价客户表`      ");
        sb.append(" 	WHERE `报价客户表`.`客户地址` IS NOT NULL AND `报价客户表`.`客户地址` <> ''     ");
        sb.append(" 	GROUP BY `报价客户表`.`电话`     ");
        sb.append("  ) AS `报价客户表` ON `报价客户表`.`电话` = `顾客记录汇总`.`联系电话`     ");
        sb.append("  LEFT JOIN `销售顾问分组` AS `销售顾问分组` ON `销售顾问分组`.`许可` = `顾客记录汇总`.`许可` AND `销售顾问分组`.`销售顾问` = `顾客记录汇总`.`所属销售顾问`     ");
        sb.append("  LEFT JOIN (     ");
        sb.append(" 	SELECT    ");
        sb.append(" 		b.`电话号码`,   ");
        sb.append(" 		b.`销售顾问`,   ");
        sb.append(" 		Max(b.`跟进日期`) 跟进日期,   ");
        sb.append(" 		COUNT(b.`电话号码`) 销售顾问跟进总次数,   ");
        sb.append(" 		(SELECT a.跟进结果 FROM 跟进记录表 a WHERE a.`许可` = b.`许可` AND a.`电话号码` = b.`电话号码` and a.`更新时间` = MAX(b.更新时间) limit 1 ) 跟进结果,   ");
        sb.append(" 		(SELECT a.战败原因 FROM 跟进记录表 a WHERE a.`许可` = b.`许可` AND a.`电话号码` = b.`电话号码` and a.`更新时间` = MAX(b.更新时间) limit 1 ) `战败原因`   ");
        sb.append(" 	FROM `跟进记录表` b  ");
        sb.append(" 	WHERE b.`许可` = :permit  ");
        sb.append(" 	GROUP BY b.`电话号码`,b.`销售顾问`    ");
        sb.append("  ) AS `跟进记录表` ON `跟进记录表`.`电话号码` = `顾客记录汇总`.`联系电话` AND `跟进记录表`.`销售顾问` = `顾客记录汇总`.`所属销售顾问`     ");
        sb.append("  LEFT JOIN (     ");
        sb.append(" 	SELECT   ");
        sb.append(" 		sc_call_history.sc,   ");
        sb.append(" 		sc_call_history.tel,   ");
        sb.append(" 		MAX(sc_call_history.上次跟进通话时长) 上次跟进通话时长   ");
        sb.append(" 	from (   ");
        sb.append(" 		SELECT   ");
        sb.append(" 			sc_call_history.tel,   ");
        sb.append(" 			sc_call_history.sc,   ");
        sb.append(" 			if(`sc_call_history`.endTime >= `sc_call_history`.startTime,TIMEDIFF(`sc_call_history`.endTime,`sc_call_history`.startTime), null) 上次跟进通话时长   ");
        sb.append(" 		FROM sc_call_history   ");
        sb.append(" 		JOIN (   ");
        sb.append(" 			SELECT    ");
        sb.append(" 				MAX(sc_call_history.updateTime) updateTime,   ");
        sb.append(" 				sc_call_history.tel,   ");
        sb.append(" 				sc_call_history.sc   ");
        sb.append(" 			from sc_call_history   ");
        sb.append(" 			GROUP BY sc_call_history.tel, sc_call_history.sc    ");
        sb.append(" 		) AS a ON a.sc = sc_call_history.sc AND a.tel = sc_call_history.tel AND a.updateTime = sc_call_history.updateTime   ");
        sb.append(" 	) sc_call_history GROUP BY sc_call_history.sc,sc_call_history.tel     ");
        sb.append("  ) AS `sc_call_history` ON `sc_call_history`.tel = `顾客记录汇总`.`联系电话` AND `sc_call_history`.sc = `顾客记录汇总`.`所属销售顾问`  ");
        sb.append("  WHERE 1 = 1 ");
        if (createDateStart != null && !"".equals(createDateStart)) {
            sb.append("     AND `顾客记录汇总`.创建日期 >= :createDateStart ");
            params.put("createDateStart", createDateStart);
        }
        if (createDateEnd != null && !"".equals(createDateEnd)) {
            sb.append("     AND `顾客记录汇总`.创建日期 <= :createDateEnd ");
            params.put("createDateEnd", createDateEnd);
        }
        if (lastFollowDateStart != null && !"".equals(lastFollowDateStart)) {
            sb.append("     AND `跟进记录表`.`跟进日期` >= :lastFollowDateStart ");
            params.put("lastFollowDateStart", lastFollowDateStart);
        }
        if (lastFollowDateEnd != null && !"".equals(lastFollowDateEnd)) {
            sb.append("     AND `跟进记录表`.`跟进日期` <= :lastFollowDateEnd ");
            params.put("lastFollowDateEnd", lastFollowDateEnd);
        }
        if (state != null && !"".equals(state)) {
            sb.append("     AND IF(`顾客进店记录表`.`顾客编号` IS NULL,'02','01') = :state ");
            params.put("state", state);
        }
        if (intentCarLevel != null && !"".equals(intentCarLevel)) {
            sb.append("     AND lsh车型.`级别` = :intentCarLevel ");
            params.put("intentCarLevel", intentCarLevel);
        }
        if (intentCarStyle != null && !"".equals(intentCarStyle)) {
            sb.append("     AND lsh车型.`车型` = :intentCarStyle ");
            params.put("intentCarStyle", intentCarStyle);
        }
        sb.append("     GROUP BY `顾客记录汇总`.`联系电话` ");
        params.put("permit", permit);
        return this.getPage(sb.toString(), params, pageSize, pageIndex);
    }

    @Override
    public List queryRateAboutSurveyTopicOfAll(String permit, String stage, String startDate, String endDate, String groupVersion, String dealerVersion) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" 	result.topicId, ");
        sb.append(" 	result.stem, ");
        sb.append(" 	GROUP_CONCAT(result.response ORDER BY result.answerId SEPARATOR '___MARK___' ) responses, ");
        sb.append(" 	GROUP_CONCAT(result.rate ORDER BY result.answerId SEPARATOR '___MARK___') rates ");
        sb.append(" FROM ");
        sb.append(" ( ");
        sb.append(" 	select  ");
        sb.append(" 		topic.topicId,topic.stem,topic.answerId,topic.response,ROUND(IFNULL(topic_rate.rate,0) * 100, 2) rate ");
        sb.append(" 	from  ");
        sb.append(" 		( ");
        sb.append(" 			select crm_topic.id topicId,crm_topic.stem,crm_answer.id answerId,crm_answer.response from crm_topic ");
        sb.append(" 			JOIN crm_answer ON crm_answer.stem_id = crm_topic.id ");
        sb.append(" 			WHERE crm_topic.stage = :stage ");

        if (groupVersion != null && !"".equals(groupVersion) && !"集团未导入调研题库".equals(groupVersion)) {
            sb.append(" 			AND crm_topic.version = :groupVersion ");
        } else {
            sb.append(" 			AND (crm_topic.version = '' OR crm_topic.version IS NULL) ");
        }

        sb.append(" 			AND crm_topic.number LIKE '%Q%' ");
        sb.append(" 			UNION ALL ");
        sb.append(" 			select crm_topic.id topicId,crm_topic.stem,crm_answer.id,crm_answer.response answerId from crm_topic ");
        sb.append(" 			JOIN crm_answer ON crm_answer.stem_id = crm_topic.id ");
        sb.append(" 			WHERE crm_topic.stage = :stage ");

        if (dealerVersion != null && !"".equals(dealerVersion) && !"经销商未导入调研题库".equals(dealerVersion)) {
            sb.append(" 			AND crm_topic.version = :dealerVersion ");
        } else {
            sb.append(" 			AND (crm_topic.version = '' OR crm_topic.version IS NULL) ");
        }

        sb.append(" 			AND crm_topic.number LIKE '%Q%' ");
        sb.append(" 		) topic ");
        sb.append(" 	LEFT JOIN  ");
        sb.append(" 		( ");
        sb.append(" 			select  ");
        sb.append(" 				topic_num.topic_id, ");
        sb.append(" 				answer_num.answer_id, ");
        sb.append(" 				answer_num.num / IF(topic_num.num = 0, 1, topic_num.num) rate ");
        sb.append(" 			FROM ");
        sb.append(" 			( ");
        sb.append(" 				select crm_flow.topic_id,COUNT(1) num from crm_history  ");
        sb.append(" 				JOIN crm_flow ON crm_flow.history_id = crm_history.id ");
        sb.append(" 				where crm_history.stage = :stage AND crm_history.permit = :permit  ");
        sb.append(" 				AND (crm_history.groupVersion IS NOT NULL OR crm_history.dealerVersion IS NOT NULL)  ");
        sb.append(" 				AND crm_history.connectTime >= :startDate  ");
        sb.append(" 				AND crm_history.connectDate <= :endDate  ");

        if (groupVersion == null || "".equals(groupVersion) || "集团未导入调研题库".equals(groupVersion)) {
            sb.append(" 				AND (crm_history.groupVersion IS NULL OR crm_history.groupVersion = '')  ");
        } else {
            sb.append(" 				AND crm_history.groupVersion = :groupVersion  ");
        }

        if (dealerVersion == null || "".equals(dealerVersion) || "经销商未导入调研题库".equals(dealerVersion)) {
            sb.append(" 				AND (crm_history.dealerVersion IS NULL OR crm_history.dealerVersion = '')   ");
        } else {
            sb.append(" 				AND crm_history.dealerVersion = :dealerVersion   ");
        }
        sb.append(" 				GROUP BY crm_flow.topic_id  ");
        sb.append(" 			) topic_num ");
        sb.append(" 			JOIN ( ");
        sb.append(" 				select crm_flow.topic_id,crm_flow.answer_id,COUNT(1) num from crm_history  ");
        sb.append(" 				JOIN crm_flow ON crm_flow.history_id = crm_history.id ");
        sb.append(" 				where crm_history.stage = :stage AND crm_history.permit = :permit  ");
        sb.append(" 				AND crm_history.connectTime >= :startDate  ");
        sb.append(" 				AND crm_history.connectDate <= :endDate  ");

        if (groupVersion == null || "".equals(groupVersion) || "集团未导入调研题库".equals(groupVersion)) {
            sb.append(" 				AND (crm_history.groupVersion IS NULL OR crm_history.groupVersion = '')  ");
        } else {
            sb.append(" 				AND crm_history.groupVersion = :groupVersion  ");
        }

        if (dealerVersion == null || "".equals(dealerVersion) || "经销商未导入调研题库".equals(dealerVersion)) {
            sb.append(" 				AND (crm_history.dealerVersion IS NULL OR crm_history.dealerVersion = '')   ");
        } else {
            sb.append(" 				AND crm_history.dealerVersion = :dealerVersion   ");
        }

        sb.append(" 				GROUP BY crm_flow.topic_id,crm_flow.answer_id ");
        sb.append(" 			) answer_num ON topic_num.topic_id = answer_num.topic_id ");
        sb.append(" 		) topic_rate ON topic.answerId = topic_rate.answer_id ");
        sb.append(" ) result GROUP BY result.topicId ");
        Map params = new HashMap();
        params.put("stage", stage);
        params.put("permit", permit);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        if (groupVersion != null && !"".equals(groupVersion) && !"集团未导入调研题库".equals(groupVersion)) {
            params.put("groupVersion", groupVersion);
        }
        if (dealerVersion != null && !"".equals(dealerVersion) && !"经销商未导入调研题库".equals(dealerVersion)) {
            params.put("dealerVersion", dealerVersion);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public List queryRateAboutSurveyTopicOfSC(String permit, String stage, String startDate, String endDate, String groupVersion, String dealerVersion) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	result.topic_id topicId, ");
        sb.append(" 	GROUP_CONCAT(result.scName SEPARATOR '___MARK___') scNames, ");
        sb.append(" 	GROUP_CONCAT(result.answerId SEPARATOR '___MARK___') answerIds, ");
        sb.append(" 	GROUP_CONCAT(result.rate SEPARATOR '___MARK___') rates ");
        sb.append(" from ( ");
        sb.append(" select  ");
        sb.append(" 	result.topic_id, ");
        sb.append(" 	result.`人员姓名` scName, ");
        sb.append(" 	GROUP_CONCAT(result.answer_id) answerId, ");
        sb.append(" 	GROUP_CONCAT(result.rate) rate ");
        sb.append(" FROM ");
        sb.append(" (select  ");
        sb.append(" 	topic_num.topic_id, ");
        sb.append(" 	answer_num.answer_id, ");
        sb.append(" 	answer_num.`人员姓名`, ");
        sb.append(" 	ROUND(answer_num.num / IF(topic_num.num = 0, 1, topic_num.num) * 100, 2) rate ");
        sb.append(" FROM ");
        sb.append(" 	( ");
        sb.append(" 		select  ");
        sb.append(" 			crm_flow.topic_id,crm_flow.answer_id,history.人员姓名,COUNT(1) num ");
        sb.append(" 		FROM ");
        sb.append(" 		( ");
        sb.append(" 			select crm_history.id,`用户`.`人员姓名` from crm_history ");
        sb.append(" 			JOIN `顾客记录汇总` ON `顾客记录汇总`.`顾客编号` = crm_history.custId ");
        sb.append(" 			JOIN `用户` ON `用户`.`用户名` = `顾客记录汇总`.`所属销售顾问` ");
        sb.append(" 			where crm_history.stage >= :stage AND  crm_history.permit = :permit  ");
        sb.append(" 			AND crm_history.connectTime >= :startDate  ");
        sb.append(" 			AND crm_history.connectDate <= :endDate  ");
        //sb.append(" 			AND crm_history.groupVersion = :groupVersion  ");

        if (groupVersion == null || "".equals(groupVersion) || "集团未导入调研题库".equals(groupVersion)) {
            sb.append(" 				AND (crm_history.groupVersion IS NULL OR crm_history.groupVersion = '')  ");
        } else {
            sb.append(" 				AND crm_history.groupVersion = :groupVersion  ");
        }

        if (dealerVersion == null || "".equals(dealerVersion) || "经销商未导入调研题库".equals(dealerVersion)) {
            sb.append(" 				AND (crm_history.dealerVersion IS NULL OR crm_history.dealerVersion = '')   ");
        } else {
            sb.append(" 				AND crm_history.dealerVersion = :dealerVersion   ");
        }

        sb.append(" 		) history ");
        sb.append(" 		JOIN crm_flow ON crm_flow.history_id = history.id ");
        sb.append(" 		GROUP BY crm_flow.topic_id,history.人员姓名 ");
        sb.append(" 	) topic_num ");
        sb.append(" JOIN ( ");
        sb.append(" 	select  ");
        sb.append(" 		crm_flow.topic_id,IFNULL(crm_flow.answer_id,'') answer_id,history.人员姓名,COUNT(1) num ");
        sb.append(" 	FROM ");
        sb.append(" 	( ");
        sb.append(" 		select crm_history.id,`用户`.`人员姓名` from crm_history ");
        sb.append(" 		JOIN `顾客记录汇总` ON `顾客记录汇总`.`顾客编号` = crm_history.custId ");
        sb.append(" 		JOIN `用户` ON `用户`.`用户名` = `顾客记录汇总`.`所属销售顾问` ");
        sb.append(" 		where crm_history.stage = :stage AND crm_history.permit = :permit ");
        sb.append(" 		AND crm_history.connectTime >= :startDate  ");
        sb.append(" 		AND crm_history.connectDate <= :endDate  ");
        //sb.append(" 		AND crm_history.groupVersion = :groupVersion  ");

        if (groupVersion == null || "".equals(groupVersion) || "集团未导入调研题库".equals(groupVersion)) {
            sb.append(" 				AND (crm_history.groupVersion IS NULL OR crm_history.groupVersion = '')  ");
        } else {
            sb.append(" 				AND crm_history.groupVersion = :groupVersion  ");
        }

        if (dealerVersion == null || "".equals(dealerVersion) || "经销商未导入调研题库".equals(dealerVersion)) {
            sb.append(" 				AND (crm_history.dealerVersion IS NULL OR crm_history.dealerVersion = '')   ");
        } else {
            sb.append(" 				AND crm_history.dealerVersion = :dealerVersion   ");
        }

        sb.append(" 	) history ");
        sb.append(" 	JOIN crm_flow ON crm_flow.history_id = history.id ");
        sb.append(" 	GROUP BY crm_flow.topic_id,crm_flow.answer_id,history.人员姓名 ");
        sb.append(" ) answer_num ON topic_num.topic_id = answer_num.topic_id AND topic_num.`人员姓名` = answer_num.`人员姓名` ");
        sb.append(" ) result GROUP BY result.topic_id,result.`人员姓名` ");
        sb.append(" ) result GROUP BY result.topic_id ");
        Map params = new HashMap();
        params.put("stage", stage);
        params.put("permit", permit);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        if (groupVersion != null && !"".equals(groupVersion) && !"集团未导入调研题库".equals(groupVersion)) {
            params.put("groupVersion", groupVersion);
        }
        if (dealerVersion != null && !"".equals(dealerVersion) && !"经销商未导入调研题库".equals(dealerVersion)) {
            params.put("dealerVersion", dealerVersion);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public List queryVersionAboutSurveyTopicForGroup(String permit, String stage, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	IFNULL(crm_history.groupVersion,'集团未导入调研题库') groupVersion ");
        sb.append(" from crm_history  ");
        sb.append(" where crm_history.permit = :permit  ");
        sb.append(" AND crm_history.stage = :stage  ");
        sb.append(" AND (crm_history.groupVersion IS NOT NULL OR crm_history.dealerVersion IS NOT NULL)  ");
        if (startDate != null && !"".equals(startDate)) {
            sb.append(" AND crm_history.connectDate >= :startDate  ");
        }
        if (endDate != null && !"".equals(endDate)) {
            sb.append(" AND crm_history.connectDate <= :endDate  ");
        }
        sb.append(" GROUP BY crm_history.groupVersion ");
        sb.append(" ORDER BY crm_history.groupVersion DESC ");
        Map params = new HashMap();
        if (startDate != null && !"".equals(startDate)) {
            params.put("startDate", startDate);
        }
        if (endDate != null && !"".equals(endDate)) {
            params.put("endDate", endDate);
        }
        params.put("permit", permit);
        params.put("stage", stage);
        return this.getList(sb.toString(), params);
    }

    @Override
    public List queryVersionAboutSurveyTopicForDealer(String permit, String stage, String startDate, String endDate, String groupVersion) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	IFNULL(crm_history.dealerVersion, '经销商未导入调研题库') dealerVersion ");
        sb.append(" from crm_history  ");
        sb.append(" where crm_history.permit = :permit  ");
        sb.append(" AND crm_history.stage = :stage  ");
        sb.append(" AND (crm_history.groupVersion IS NOT NULL OR crm_history.dealerVersion IS NOT NULL)  ");
        if (startDate != null && !"".equals(startDate)) {
            sb.append(" AND crm_history.connectDate >= :startDate  ");
        }
        if (endDate != null && !"".equals(endDate)) {
            sb.append(" AND crm_history.connectDate <= :endDate  ");
        }
        if (groupVersion != null && !"".equals(groupVersion) && !"集团未导入调研题库".equals(groupVersion)) {
            sb.append(" AND crm_history.groupVersion = :groupVersion  ");
        }
        sb.append(" GROUP BY crm_history.dealerVersion ");
        sb.append(" ORDER BY crm_history.dealerVersion DESC ");
        Map params = new HashMap();
        if (startDate != null && !"".equals(startDate)) {
            params.put("startDate", startDate);
        }
        if (endDate != null && !"".equals(endDate)) {
            params.put("endDate", endDate);
        }
        params.put("permit", permit);
        params.put("stage", stage);
        if (groupVersion != null && !"".equals(groupVersion) && !"集团未导入调研题库".equals(groupVersion)) {
            params.put("groupVersion", groupVersion);
        }
        return this.getList(sb.toString(), params);
    }

    public List queryCrmHistoryLogInfo(String historyId) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append("SELECT " +
                " 	a.id, " +
                " 	a.history_id, " +
                " 	date_format(a.updateTime, '%Y-%m-%d') connectDate, " +
                " 	CASE " +
                " 		WHEN a.surveyResult = '1' THEN '完成问卷' " +
                " 		WHEN a.surveyResult = '2' THEN '待跟进' " +
                " 		WHEN a.surveyResult = '3' THEN '无效电话' " +
                " 		WHEN a.surveyResult = '4' THEN '拒绝回访' " +
                " 		WHEN a.surveyResult = '5' THEN '重新分配' " +
                " 		WHEN a.surveyResult = '6' THEN '投诉' " +
                " 		WHEN a.surveyResult = '7' THEN '激活并分配' " +
                " 		WHEN a.surveyResult = '8' THEN '推荐名单推送' " +
                " 	ELSE '' end AS surveyResult, " +
                " 	a.nextConnectDate, " +
                " 	a.remark " +
                " FROM crm_history_log a " +
                " WHERE a.history_id = :historyId ");
        param.put("historyId", historyId);
        return this.getList(sb.toString(), param);
    }

    @Override
    public List findSurveyInfoForFirstSurvey(String permit, String username, String firstToShopDate, String NPSDate, String stage, String cusName,
                                             String cusPhone, String researchState, String connectStartDate, String connectEndDate, String nowState,
                                             String intentCarVehicles, String intentCarStyle, String nextConnectStartDate, String nextConnectEndDate,
                                             String surveyResult, int pageSize, int pageSizeStart) {
        StringBuffer sql = new StringBuffer();
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("firstToShopDate", firstToShopDate);
        params.put("pageSize", pageSize);
        params.put("pageSizeStart", pageSizeStart);
        sql.append(" SELECT ");
        sql.append(" 	'' id, '01' AS stage, '首次离店调研' AS stageName,'' surveyResult,  ");
        sql.append(" 	'' valid, a.`顾客编号` AS custId,b.`顾客姓名` cusName,  ");
        sql.append(" 	case b.`顾客性别` when '男' then '先生' when '女' then '女士' else '' end cusAppellation, ");
        sql.append(" 	b.`电话号码` cusPhone,a.`进店日期` AS firstStoreDate, ");
        sql.append(" 	IF(d.`来源` = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) intentCarVehicles,  ");
        sql.append(" 	d.`车型` intentCarStyle,a.`销售顾问` scCode,e.`人员姓名` scName,  ");
        sql.append(" 	'' orderIntervalDays,'' connectDate,'' connectTime,'' nextConnectDate,  ");
        sql.append(" 	a.`进店编号` AS serialNumber ");
        sql.append(" FROM `顾客进店记录表` AS a ");
        sql.append(" LEFT JOIN ( ");
        sql.append(" 	SELECT b.serialNumber from crm_history AS b where b.permit = :permit AND b.stage = '01' ");
        sql.append(" ) m ON a.`进店编号` = m.serialNumber ");
        sql.append(" JOIN `顾客记录汇总` as b on a.顾客编号 = b.`顾客编号`  ");
        sql.append(" LEFT JOIN `lsh车型` as d ON a.`意向车型A` = d.`车型编号`  ");
        sql.append(" LEFT JOIN `用户` as e ON e.`用户名` = a.`销售顾问` ");
        sql.append(" WHERE a.`许可` = :permit AND a.`顾客编号` IS NOT NULL ");
        sql.append(" AND a.`是否首次到店` = '是' AND a.`接待类型` <> '未到店' ");
        sql.append(" AND a.`进店日期` <= :firstToShopDate AND a.`到店时间` <> '00:00:00' ");
        sql.append(" AND a.`到店时间` <> '01:00:00' AND a.`到店时间` <> '03:00:00' ");
        sql.append(" AND m.serialNumber IS NULL ");
        if (!StringUtils.isNullOrBlank(cusName)) {
            sql.append(" 	AND b.顾客姓名 LIKE :cusName ");
            params.put("cusName", "%" + cusName + "%");
        }
        if (!StringUtils.isNullOrBlank(cusPhone)) {
            sql.append(" 	AND b.电话号码 like :cusPhone ");
            params.put("cusPhone", "%" + cusPhone + "%");
        }
        if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
            sql.append(" 	AND IF(d.来源 = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) = :intentCarVehicles ");
            params.put("intentCarVehicles", intentCarVehicles);
        }
        if (!StringUtils.isNullOrBlank(intentCarStyle)) {
            sql.append(" 	AND d.车型 = :intentCarStyle ");
            params.put("intentCarStyle", intentCarStyle);
        }
        if ("EYZL10".equals(permit)) {
            sql.append(" 	and a.`进店日期` >= '2017-01-17' ");
        }
        sql.append(" ORDER BY a.`进店编号` LIMIT :pageSizeStart,:pageSize ");
        return this.getList(sql.toString(), params);
    }

    @Override
    public List findSurveyInfoForNPSSurvey(String permit, String username, String firstToShopDate, String NPSDate, String stage, String cusName,
                                           String cusPhone, String researchState, String connectStartDate, String connectEndDate, String nowState,
                                           String intentCarVehicles, String intentCarStyle, String nextConnectStartDate, String nextConnectEndDate,
                                           String surveyResult, int pageSize, int pageSizeStart) {
        StringBuffer sql = new StringBuffer();
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("NPSDate", NPSDate);
        params.put("pageSize", pageSize);
        params.put("pageSizeStart", pageSizeStart);
        sql.append(" SELECT ");
        sql.append(" 	'' id,'04' AS stage,'NPS调研' AS stageName,'' surveyResult, ");
        sql.append(" 	'' valid,a.`顾客编号` AS custId,b.`顾客姓名` cusName, ");
        sql.append(" 	case b.`顾客性别` when '男' then '先生' when '女' then '女士' else '' end cusAppellation, ");
        sql.append(" 	b.`电话号码` cusPhone, b.`留档日期` firstStoreDate, ");
        sql.append(" 	IF(d.`来源` = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) intentCarVehicles,  ");
        sql.append(" 	d.`车型` intentCarStyle,a.`销售顾问` scCode,e.`人员姓名` scName,  ");
        sql.append(" 	'' orderIntervalDays,'' connectDate,'' connectTime,'' nextConnectDate, ");
        sql.append(" 	a.`订单编号` AS serialNumber ");
        sql.append(" FROM `顾客订单记录` AS a ");
        sql.append(" LEFT JOIN ( ");
        sql.append(" 	SELECT b.serialNumber from crm_history AS b where b.permit = :permit AND b.stage = '04' ");
        sql.append(" ) m ON a.`订单编号` = m.serialNumber ");
        sql.append(" JOIN `顾客记录汇总` as b on a.顾客编号 = b.`顾客编号`  ");
        sql.append(" LEFT JOIN `lsh车型` as d ON b.`意向车型A` = d.`车型编号`  ");
        sql.append(" LEFT JOIN `用户` as e ON e.`用户名` = a.`销售顾问` ");
        sql.append(" WHERE a.`许可` = :permit AND a.`交车日期` IS NOT NULL ");
        sql.append(" AND a.`交车日期` <= :NPSDate AND m.serialNumber IS NULL ");
        if (!StringUtils.isNullOrBlank(cusName)) {
            sql.append(" 	AND b.顾客姓名 LIKE :cusName ");
            params.put("cusName", "%" + cusName + "%");
        }
        if (!StringUtils.isNullOrBlank(cusPhone)) {
            sql.append(" 	AND b.电话号码 like :cusPhone ");
            params.put("cusPhone", "%" + cusPhone + "%");
        }
        if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
            sql.append(" 	AND IF(d.来源 = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) = :intentCarVehicles ");
            params.put("intentCarVehicles", intentCarVehicles);
        }
        if (!StringUtils.isNullOrBlank(intentCarStyle)) {
            sql.append(" 	AND d.车型 = :intentCarStyle ");
            params.put("intentCarStyle", intentCarStyle);
        }
        sql.append(" ORDER BY a.`订单编号` LIMIT :pageSizeStart,:pageSize ");
        return this.getList(sql.toString(), params);
    }

    @Override
    public List findSurveyInfoForFailOrSleepSurvey(String permit, String username, String firstToShopDate, String NPSDate, String stage, String cusName,
                                                   String cusPhone, String researchState, String connectStartDate, String connectEndDate, String nowState,
                                                   String intentCarVehicles, String intentCarStyle, String nextConnectStartDate, String nextConnectEndDate,
                                                   String surveyResult, int pageSize, int pageSizeStart) {
        StringBuffer sql = new StringBuffer();
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("pageSize", pageSize);
        params.put("pageSizeStart", pageSizeStart);
        sql.append(" SELECT ");
        sql.append(" 	'' id, ");
        sql.append(" 	CASE a.`顾客状态` WHEN '休眠' THEN '02' WHEN '战败' THEN '03' ELSE '' END AS stage, ");
        sql.append(" 	CASE a.`顾客状态` WHEN '休眠' THEN '休眠调研' WHEN '战败' THEN '战败调研' ELSE '' END AS stageName, ");
        sql.append(" 	'' surveyResult,'' valid,a.`顾客编号` AS custId,a.`顾客姓名` cusName, ");
        sql.append(" 	case a.`顾客性别` when '男' then '先生' when '女' then '女士' else '' end cusAppellation, ");
        sql.append(" 	a.`电话号码` cusPhone,a.`留档日期` firstStoreDate, ");
        sql.append(" 	IF(d.`来源` = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) intentCarVehicles,  ");
        sql.append(" 	d.`车型` intentCarStyle,a.`销售顾问` scCode,e.`人员姓名` scName,  ");
        sql.append(" 	'' orderIntervalDays,'' connectDate,'' connectTime,'' nextConnectDate, ");
        sql.append(" 	'' serialNumber ");
        sql.append(" FROM `顾客记录汇总` a ");
        sql.append(" LEFT JOIN ( ");
        sql.append(" 	SELECT b.id,b.stage,b.custId from crm_history b WHERE b.permit = :permit AND (b.stage = '02' OR b.stage = '03') ");
        sql.append(" ) m ON (CASE a.`顾客状态` WHEN '休眠' THEN '02' WHEN '战败' THEN '03' ELSE '' END = m.stage AND a.顾客编号 = m.custId) ");
        sql.append(" LEFT JOIN `lsh车型` as d ON a.`意向车型A` = d.`车型编号`  ");
        sql.append(" LEFT JOIN `用户` as e ON e.`用户名` = a.`所属销售顾问` ");
        sql.append(" WHERE a.`许可` = :permit ");
        sql.append(" AND a.`CRM战败确认` IS NULL ");
        sql.append(" AND m.id is null ");
        if ("02".equals(stage)) {
            sql.append(" AND a.`顾客状态` = '休眠' ");
        } else if ("03".equals(stage)) {
            sql.append(" AND a.`顾客状态` = '战败' ");
        } else {
            sql.append(" AND (a.`顾客状态` = '休眠' OR a.`顾客状态` = '战败') ");
        }
        if (!StringUtils.isNullOrBlank(cusName)) {
            sql.append(" 	AND a.顾客姓名 LIKE :cusName ");
            params.put("cusName", "%" + cusName + "%");
        }
        if (!StringUtils.isNullOrBlank(cusPhone)) {
            sql.append(" 	AND a.电话号码 like :cusPhone ");
            params.put("cusPhone", "%" + cusPhone + "%");
        }
        if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
            sql.append(" 	AND IF(d.来源 = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) = :intentCarVehicles ");
            params.put("intentCarVehicles", intentCarVehicles);
        }
        if (!StringUtils.isNullOrBlank(intentCarStyle)) {
            sql.append(" 	AND d.车型 = :intentCarStyle ");
            params.put("intentCarStyle", intentCarStyle);
        }
        sql.append(" ORDER BY a.`顾客编号` LIMIT :pageSizeStart, :pageSize ");
        return this.getList(sql.toString(), params);
    }

    @Override
    public List findSurveyInfoForAlreadySurvey(String permit, String username, String firstToShopDate, String NPSDate, String stage, String cusName,
                                               String cusPhone, String researchState, String connectStartDate, String connectEndDate, String nowState,
                                               String intentCarVehicles, String intentCarStyle, String nextConnectStartDate, String nextConnectEndDate,
                                               String surveyResult, int pageSize, int pageSizeStart) {
        StringBuffer sql = new StringBuffer();
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("username", username);
        params.put("pageSize", pageSize);
        params.put("pageSizeStart", pageSizeStart);
        sql.append(" select  ");
        sql.append(" 	crm_history.id,crm_history.stage, ");
        sql.append(" 	CASE crm_history.stage ");
        sql.append(" 	WHEN '01' THEN '首次离店调研' ");
        sql.append(" 	WHEN '02' THEN '休眠调研' ");
        sql.append(" 	WHEN '03' THEN '战败调研' ");
        sql.append(" 	WHEN '04' THEN 'NPS调研' ");
        sql.append(" 	END stageName, ");
        sql.append(" 	CASE crm_history.surveyResult ");
        sql.append(" 	WHEN '1' THEN '完成问卷' ");
        sql.append(" 	WHEN '2' THEN '待跟进' ");
        sql.append(" 	WHEN '3' THEN '无效电话' ");
        sql.append(" 	WHEN '4' THEN '拒绝回访' ");
        sql.append(" 	WHEN '5' THEN '重新分配' ");
        sql.append(" 	WHEN '6' THEN '投诉' ");
        sql.append(" 	WHEN '7' THEN '激活并分配' ");
        sql.append(" 	WHEN '8' THEN '推荐名单推送' ");
        sql.append(" 	ELSE '' END surveyResult, ");
        sql.append(" 	CASE crm_history.valid WHEN '0' THEN '无效' WHEN '1' THEN '有效' ELSE '' END valid, ");
        sql.append(" 	crm_history.custId,`顾客记录汇总`.`顾客姓名` cusName, ");
        sql.append(" 	case ");
        sql.append(" 		when `顾客记录汇总`.`顾客性别` = '男' then '先生' ");
        sql.append(" 		when `顾客记录汇总`.`顾客性别` = '女' then '女士' ");
        sql.append(" 	else '' end cusAppellation, ");
        sql.append(" 	`顾客记录汇总`.`电话号码` cusPhone, ");
        sql.append(" 	IF(crm_history.stage = '01', `顾客进店记录表`.进店日期, c.firstStoreDate) firstStoreDate, ");
        sql.append(" 	IF(`lsh车型`.`来源` = 'PbP', CONCAT('PbP ', `lsh车型`.`级别`), `lsh车型`.`级别`) intentCarVehicles, ");
        sql.append(" 	`lsh车型`.`车型` intentCarStyle,`顾客记录汇总`.`所属销售顾问` scCode,`用户`.`人员姓名` scName, ");
        sql.append(" 	datediff(CURDATE(),crm_history.connectDate) orderIntervalDays, ");
        sql.append(" 	crm_history.connectDate,crm_history.connectTime,crm_history.nextConnectDate, ");
        sql.append(" 	crm_history.serialNumber ");
        sql.append(" from crm_history as crm_history ");
        sql.append(" JOIN `顾客记录汇总` as `顾客记录汇总` ON crm_history.custId = `顾客记录汇总`.`顾客编号` ");
        sql.append(" LEFT JOIN `顾客进店记录表` as `顾客进店记录表` ON (`顾客进店记录表`.`进店编号` = crm_history.serialNumber AND crm_history.stage = '01') ");
        sql.append(" LEFT JOIN `顾客订单记录` as `顾客订单记录` ON (`顾客订单记录`.`订单编号` = crm_history.serialNumber AND crm_history.stage = '04') ");
        sql.append(" LEFT JOIN `lsh车型` as `lsh车型` ON `lsh车型`.`车型编号` = IF(crm_history.stage <> '01', IF(crm_history.stage <> '04',`顾客记录汇总`.`意向车型A`, `顾客订单记录`.车型), `顾客进店记录表`.`意向车型A`) ");
        sql.append(" LEFT JOIN `用户` AS `用户` ON `用户`.`用户名` = IF(crm_history.stage <> '01',`顾客记录汇总`.`所属销售顾问`,`顾客进店记录表`.`销售顾问`) ");
        sql.append(" LEFT JOIN ( ");
        sql.append(" 	SELECT a.`顾客编号`,MIN(a.`进店日期`) firstStoreDate FROM `顾客进店记录表` a ");
        sql.append(" 	WHERE a.`许可` = :permit ");
        sql.append(" 	AND a.`顾客编号` IS NOT NULL ");
        sql.append(" 	AND a.`是否首次到店` = '是' ");
        sql.append(" 	GROUP BY a.`顾客编号` ");
        sql.append(" ) c ON crm_history.custId = c.`顾客编号` ");
        sql.append(" WHERE crm_history.permit = :permit AND crm_history.updateAccount = :username ");
        if (!StringUtils.isNullOrBlank(stage) && !"00".equals(stage)) {
            sql.append(" and crm_history.stage = :stage ");
            params.put("stage", stage);
        }
        if (!StringUtils.isNullOrBlank(surveyResult)) {
            sql.append(" 	AND crm_history.surveyResult = :surveyResult ");
            params.put("surveyResult", surveyResult);
        }
        if (!StringUtils.isNullOrBlank(connectStartDate)) {
            sql.append(" 	AND crm_history.connectDate >= :connectStartDate  ");
            params.put("connectStartDate", connectStartDate);
        }
        if (!StringUtils.isNullOrBlank(connectEndDate)) {
            sql.append(" 	AND crm_history.connectDate <= :connectEndDate ");
            params.put("connectEndDate", connectEndDate);
        }
        if (!StringUtils.isNullOrBlank(nextConnectStartDate)) {
            sql.append(" 	AND DATE_FORMAT(crm_history.nextConnectDate,'%Y-%m-%d') >= :nextConnectStartDate ");
            params.put("nextConnectStartDate", nextConnectStartDate);
        }
        if (!StringUtils.isNullOrBlank(nextConnectEndDate)) {
            sql.append(" 	AND DATE_FORMAT(crm_history.nextConnectDate,'%Y-%m-%d') <= :nextConnectEndDate ");
            params.put("nextConnectEndDate", nextConnectEndDate);
        }
        if (!StringUtils.isNullOrBlank(cusName)) {
            sql.append(" 	AND 顾客记录汇总.顾客姓名 LIKE :cusName ");
            params.put("cusName", "%" + cusName + "%");
        }
        if (!StringUtils.isNullOrBlank(cusPhone)) {
            sql.append(" 	AND 顾客记录汇总.电话号码 like :cusPhone ");
            params.put("cusPhone", "%" + cusPhone + "%");
        }
        if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
            sql.append(" 	AND IF(`lsh车型`.来源 = 'PbP', CONCAT('PbP ', `lsh车型`.`级别`), `lsh车型`.`级别`) = :intentCarVehicles ");
            params.put("intentCarVehicles", intentCarVehicles);
        }
        if (!StringUtils.isNullOrBlank(intentCarStyle)) {
            sql.append(" 	AND `lsh车型`.车型 = :intentCarStyle ");
            params.put("intentCarStyle", intentCarStyle);
        }
        sql.append(" ORDER BY crm_history.id LIMIT :pageSizeStart,:pageSize ");
        return this.getList(sql.toString(), params);

    }

    @Override
    public List findSurveyInfoForCount(String permit, String username, String firstToShopDate, String NPSDate, String stage, String cusName,
                                       String cusPhone, String researchState, String connectStartDate, String connectEndDate, String nowState,
                                       String intentCarVehicles, String intentCarStyle, String nextConnectStartDate, String nextConnectEndDate,
                                       String surveyResult, int pageSize, int pageSizeStart) {
        StringBuffer sql = new StringBuffer();
        Map params = new HashMap();
        params.put("permit", permit);

        params.put("username", username);
        sql.append(" select  ");
        sql.append(" 	'alreadySurvey' state,count(1) countNum ");
        sql.append(" from crm_history as crm_history ");
        sql.append(" JOIN `顾客记录汇总` as `顾客记录汇总` ON crm_history.custId = `顾客记录汇总`.`顾客编号` ");
        sql.append(" LEFT JOIN `顾客进店记录表` as `顾客进店记录表` ON (`顾客进店记录表`.`进店编号` = crm_history.serialNumber AND crm_history.stage = '01') ");
        sql.append(" LEFT JOIN `顾客订单记录` as `顾客订单记录` ON (`顾客订单记录`.`订单编号` = crm_history.serialNumber AND crm_history.stage = '04') ");
        sql.append(" LEFT JOIN `lsh车型` as `lsh车型` ON `lsh车型`.`车型编号` = IF(crm_history.stage <> '01', IF(crm_history.stage <> '04',`顾客记录汇总`.`意向车型A`, `顾客订单记录`.车型), `顾客进店记录表`.`意向车型A`) ");
        sql.append(" WHERE crm_history.permit = :permit AND crm_history.updateAccount = :username ");
        if (!StringUtils.isNullOrBlank(stage) && !"00".equals(stage)) {
            sql.append(" AND crm_history.stage = :stage ");
            params.put("stage", stage);
        }
        if (!StringUtils.isNullOrBlank(surveyResult)) {
            sql.append(" 	AND crm_history.surveyResult = :surveyResult ");
            params.put("surveyResult", surveyResult);
        }
        if (!StringUtils.isNullOrBlank(connectStartDate)) {
            sql.append(" 	AND crm_history.connectDate >= :connectStartDate  ");
            params.put("connectStartDate", connectStartDate);
        }
        if (!StringUtils.isNullOrBlank(connectEndDate)) {
            sql.append(" 	AND crm_history.connectDate <= :connectEndDate ");
            params.put("connectEndDate", connectEndDate);
        }
        if (!StringUtils.isNullOrBlank(nextConnectStartDate)) {
            sql.append(" 	AND DATE_FORMAT(crm_history.nextConnectDate,'%Y-%m-%d') >= :nextConnectStartDate ");
            params.put("nextConnectStartDate", nextConnectStartDate);
        }
        if (!StringUtils.isNullOrBlank(nextConnectEndDate)) {
            sql.append(" 	AND DATE_FORMAT(crm_history.nextConnectDate,'%Y-%m-%d') <= :nextConnectEndDate ");
            params.put("nextConnectEndDate", nextConnectEndDate);
        }
        if (!StringUtils.isNullOrBlank(cusName)) {
            sql.append(" 	AND 顾客记录汇总.顾客姓名 LIKE :cusName ");
            params.put("cusName", "%" + cusName + "%");
        }
        if (!StringUtils.isNullOrBlank(cusPhone)) {
            sql.append(" 	AND 顾客记录汇总.电话号码 like :cusPhone ");
            params.put("cusPhone", "%" + cusPhone + "%");
        }
        if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
            sql.append(" 	AND IF(`lsh车型`.来源 = 'PbP', CONCAT('PbP ', `lsh车型`.`级别`), `lsh车型`.`级别`) = :intentCarVehicles ");
            params.put("intentCarVehicles", intentCarVehicles);
        }
        if (!StringUtils.isNullOrBlank(intentCarStyle)) {
            sql.append(" 	AND `lsh车型`.车型 = :intentCarStyle ");
            params.put("intentCarStyle", intentCarStyle);
        }
        if (StringUtils.isNullOrBlank(connectStartDate) && StringUtils.isNullOrBlank(connectEndDate)
                && StringUtils.isNullOrBlank(nextConnectStartDate) && StringUtils.isNullOrBlank(nextConnectEndDate)
                && StringUtils.isNullOrBlank(surveyResult)) {
            if (StringUtils.isNullOrBlank(stage) || "00".equals(stage) || "01".equals(stage)) {
                params.put("firstToShopDate", firstToShopDate);
                sql.append(" union all ");
                sql.append(" SELECT ");
                sql.append(" 	'firstSurvey' state,count(1) countNum ");
                sql.append(" FROM `顾客进店记录表` AS a ");
                sql.append(" LEFT JOIN ( ");
                sql.append(" 	SELECT b.serialNumber from crm_history AS b where b.permit = :permit AND b.stage = '01' ");
                sql.append(" ) m ON a.`进店编号` = m.serialNumber ");
                sql.append(" JOIN `顾客记录汇总` as b on a.顾客编号 = b.`顾客编号`  ");
                sql.append(" LEFT JOIN `lsh车型` as d ON a.`意向车型A` = d.`车型编号`  ");
                sql.append(" WHERE a.`许可` = :permit AND a.`顾客编号` IS NOT NULL ");
                sql.append(" AND a.`是否首次到店` = '是' AND a.`接待类型` <> '未到店' ");
                sql.append(" AND a.`进店日期` <= :firstToShopDate AND a.`到店时间` <> '00:00:00' ");
                sql.append(" AND a.`到店时间` <> '01:00:00' AND a.`到店时间` <> '03:00:00' ");
                sql.append(" AND m.serialNumber IS NULL ");
                if (!StringUtils.isNullOrBlank(cusName)) {
                    sql.append(" 	AND b.顾客姓名 LIKE :cusName ");
                    params.put("cusName", "%" + cusName + "%");
                }
                if (!StringUtils.isNullOrBlank(cusPhone)) {
                    sql.append(" 	AND b.电话号码 like :cusPhone ");
                    params.put("cusPhone", "%" + cusPhone + "%");
                }
                if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
                    sql.append(" 	AND IF(d.来源 = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) = :intentCarVehicles ");
                    params.put("intentCarVehicles", intentCarVehicles);
                }
                if (!StringUtils.isNullOrBlank(intentCarStyle)) {
                    sql.append(" 	AND d.车型 = :intentCarStyle ");
                    params.put("intentCarStyle", intentCarStyle);
                }
                if ("EYZL10".equals(permit)) {
                    sql.append(" 	and a.`进店日期` >= '2017-01-17' ");
                }
            }
            if (StringUtils.isNullOrBlank(stage) || "00".equals(stage) || "04".equals(stage)) {
                params.put("NPSDate", NPSDate);
                sql.append(" union all ");
                sql.append(" SELECT ");
                sql.append(" 	'NPSSurvey' state,count(1) countNum ");
                sql.append(" FROM `顾客订单记录` AS a ");
                sql.append(" LEFT JOIN ( ");
                sql.append(" 	SELECT b.serialNumber from crm_history AS b where b.permit = :permit AND b.stage = '04' ");
                sql.append(" ) m ON a.`订单编号` = m.serialNumber ");
                sql.append(" JOIN `顾客记录汇总` as b on a.顾客编号 = b.`顾客编号`  ");
                sql.append(" LEFT JOIN `lsh车型` as d ON b.`意向车型A` = d.`车型编号`  ");
                sql.append(" WHERE a.`许可` = :permit AND a.`交车日期` IS NOT NULL ");
                sql.append(" AND a.`交车日期` <= :NPSDate AND m.serialNumber IS NULL ");
                if (!StringUtils.isNullOrBlank(cusName)) {
                    sql.append(" 	AND b.顾客姓名 LIKE :cusName ");
                    params.put("cusName", "%" + cusName + "%");
                }
                if (!StringUtils.isNullOrBlank(cusPhone)) {
                    sql.append(" 	AND b.电话号码 like :cusPhone ");
                    params.put("cusPhone", "%" + cusPhone + "%");
                }
                if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
                    sql.append(" 	AND IF(d.来源 = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) = :intentCarVehicles ");
                    params.put("intentCarVehicles", intentCarVehicles);
                }
                if (!StringUtils.isNullOrBlank(intentCarStyle)) {
                    sql.append(" 	AND d.车型 = :intentCarStyle ");
                    params.put("intentCarStyle", intentCarStyle);
                }
            }
            if (StringUtils.isNullOrBlank(stage) || "00".equals(stage) || "02".equals(stage) || "03".equals(stage)){
                sql.append(" union all ");
                sql.append(" SELECT ");
                sql.append(" 	'failOrSleepSurvey' state,count(1) countNum ");
                sql.append(" FROM `顾客记录汇总` a ");
                sql.append(" LEFT JOIN ( ");
                sql.append(" 	SELECT b.id,b.stage,b.custId from crm_history b WHERE b.permit = :permit AND (b.stage = '02' OR b.stage = '03') ");
                sql.append(" ) m ON (CASE a.`顾客状态` WHEN '休眠' THEN '02' WHEN '战败' THEN '03' ELSE '' END = m.stage AND a.顾客编号 = m.custId) ");
                sql.append(" LEFT JOIN `lsh车型` as d ON a.`意向车型A` = d.`车型编号`  ");
                sql.append(" WHERE a.`许可` = :permit ");
                sql.append(" AND a.`CRM战败确认` IS NULL ");
                sql.append(" AND m.id is null ");
                if ("02".equals(stage)) {
                    sql.append(" AND a.`顾客状态` = '休眠' ");
                } else if ("03".equals(stage)) {
                    sql.append(" AND a.`顾客状态` = '战败' ");
                } else {
                    sql.append(" AND (a.`顾客状态` = '休眠' OR a.`顾客状态` = '战败') ");
                }
                if (!StringUtils.isNullOrBlank(cusName)) {
                    sql.append(" 	AND a.顾客姓名 LIKE :cusName ");
                    params.put("cusName", "%" + cusName + "%");
                }
                if (!StringUtils.isNullOrBlank(cusPhone)) {
                    sql.append(" 	AND a.电话号码 like :cusPhone ");
                    params.put("cusPhone", "%" + cusPhone + "%");
                }
                if (!StringUtils.isNullOrBlank(intentCarVehicles)) {
                    sql.append(" 	AND IF(d.来源 = 'PbP', CONCAT('PbP ', d.`级别`), d.`级别`) = :intentCarVehicles ");
                    params.put("intentCarVehicles", intentCarVehicles);
                }
                if (!StringUtils.isNullOrBlank(intentCarStyle)) {
                    sql.append(" 	AND d.车型 = :intentCarStyle ");
                    params.put("intentCarStyle", intentCarStyle);
                }
            }
        }
        return this.getList(sql.toString(), params);
    }
}
